package huizhi.service.flower;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Optimize {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
//        allValue("1704435229558108162","10","2023-11-05","2023-11-08");
//        allValue("1704435229558108162",null,"",null);

        allValue(null,"1581","",null);

    }

    /**
     * /**
     *  * 查询条件：
     *  * 	地区、品类、时间区间(yyyy-MM-dd)
     *  *
     *  * 返回值：
     *  * 	返回上述满足条件的数据
     *  * 		客户数量
     *  * 		发货金额
     *  * 		各个地区的统计：地区名称+金额
     *  */
//     * @param baseId 基地值
//     * @param areaId 地区值
//     * @param localDate1 时间1
//     * @param localDate2 时间2
//     * @throws SQLException
//     * @throws ClassNotFoundException
//     */
    public static void allValue(String baseId, String areaId, String localDate1,String localDate2) throws SQLException, ClassNotFoundException {
        //获取连接对象
        Connection connection = ConnectionRequest.requestConnection();
        //获取statement对象
        Statement statement = connection.createStatement();
//        String sql = "select count(DISTINCT bis_flower_order.custom_id)," +
//                "sum(bis_flower_order.goods_amount) from bis_flower_order " +
//                "LEFT JOIN bis_custom_delivery_address on bis_flower_order.custom_id = " +
//                "bis_custom_delivery_address.custom_id where \n" +
//                "bis_flower_order.base_id = 1704435229558108162 and bis_custom_delivery_address.area_id " +
//                "= 10 and DATE_FORMAT(bis_flower_order.place_order_time, '%Y-%m-%d')" +
//                " BETWEEN '2023-11-05' AND '2023-11-08';";
        //定义两个sql语句 分别查询 总人数总金额 和 按地区分组查询金额
        String sql = "select count(DISTINCT bis_flower_order.custom_id), " +
                "sum(bis_flower_order.goods_amount) from bis_flower_order " +
                "LEFT JOIN bis_custom_delivery_address on bis_flower_order.custom_id = " +
                "bis_custom_delivery_address.custom_id " +
                "LEFT JOIN china_area on bis_custom_delivery_address.area_id= china_area.id ";
        String sql2 = "select china_area.full_name,sum(bis_flower_order.goods_amount) " +
                " from bis_flower_order LEFT JOIN bis_custom_delivery_address on bis_flower_order.custom_id " +
                " = bis_custom_delivery_address.custom_id LEFT JOIN china_area on bis_custom_delivery_address.area_id " +
                " = china_area.id  ";
        //查省份
        String areaCode = "";
        if (StrUtil.isNotEmpty(areaId)) {
            //查出areacode
            String sql3 = "select area_code from china_area where id = " + areaId;
            ResultSet resultSet = statement.executeQuery(sql3);
            while (resultSet.next()) {
                areaCode = resultSet.getString(1);
            }
        }
        //查省份加地区
        String areaCode2 = "";
        if (StrUtil.isNotEmpty(areaId)) {
            //查出areacode
            String sql3 = "select area_code from china_area where id = " + areaId;
            ResultSet resultSet = statement.executeQuery(sql3);
            while (resultSet.next()) {
                areaCode2 = resultSet.getString(1);
            }
        }

        //实例实体类 包装结果
        Costom costom = new Costom();
        //实例地区和数量实体类
        List<RegionalAmount> regionalAmountList = new ArrayList<>();
        //定义sqlString集合 用于存储需要的动态sql语句
        ArrayList<String> sqlString = CollectionUtil.newArrayList();

        //判断参数是否为空 并将数据添加到集合中
        if (StrUtil.isNotEmpty(baseId)) {
            sqlString.add(StrUtil.format("bis_flower_order.base_id = '{}' ", baseId));
        }
        //按省份地区查询
//        if (StrUtil.isNotEmpty(areaId)){
//            sqlString.add(StrUtil.format("bis_custom_delivery_address.area_id = '{}' ",areaId));
//        }
        //定义参数判断查哪一个 1为只查省份，2为查省份和地区
        int a = 2;
        if (a == 1) {
            if (StrUtil.isNotEmpty(areaId)) {
                areaCode = areaCode.substring(0, 2);
                sqlString.add(StrUtil.format(" china_area.area_code like '{}%' ", areaCode));
            }
        } else if (a == 2) {
            if (StrUtil.isNotEmpty(areaId)) {
                areaCode2 = areaCode.substring(0, 4);
                sqlString.add(StrUtil.format(" china_area.area_code like '{}%' ", areaCode2));
            }}

            //按省份查询
//        if (StrUtil.isNotEmpty(areaId)){
//            areaCode = areaCode.substring(0,2);
//            sqlString.add(StrUtil.format(" china_area.area_code like '{}%' ",areaCode));
//        }
            //按省份 地区 查询 areaCode2
//        if (StrUtil.isNotEmpty(areaId)){
//            areaCode2 = areaCode.substring(0,4);
//            sqlString.add(StrUtil.format(" china_area.area_code like '{}%' ",areaCode2));
//        }

            if (StrUtil.isAllNotEmpty(localDate1, localDate2)) {
                sqlString.add(StrUtil.format("DATE_FORMAT(bis_flower_order.place_order_time, '%Y-%m-%d') " +
                        " BETWEEN '{}' AND '{}';", localDate1, localDate2));
            }

            if (CollectionUtil.isNotEmpty(sqlString)) {
                sql += "where {}";
                sql2 += "where {}";
                sql = StrUtil.format(sql, CollectionUtil.join(sqlString, " and "));
                sql2 = StrUtil.format(sql2, CollectionUtil.join(sqlString, " and "));
//            sql2 += sqlString.get(0);

            }
            //为sql语句进行最后的拼接 使其可以实现按组查询金额的功能
            sql2 += " GROUP BY china_area.id; ";
//        System.out.println(sql2);
            //下面都是查询
            ResultSet resultSet = statement.executeQuery(sql);
            System.out.println("基地为" + baseId + " 地区为" + areaId + " 时间范围在 " + localDate1 + "-" + localDate2 + "之间的数据为");
            while (resultSet.next()) {
                //添加结果到实体类中进行包装
                costom.setPeopleNum(Integer.parseInt(resultSet.getString(1)));
                costom.setTotalAmount(Double.parseDouble(resultSet.getString(2)));
            }

            //打印结果看是否存储
            System.out.println(costom.toString());
//        System.out.println(costom.getPeopleNum());
//        System.out.println(costom.getTotalAmount());

//        System.out.println(sql2);
//        String sql2 ="select china_area.full_name,sum(bis_flower_order.goods_amount) " +
//                "from bis_flower_order LEFT JOIN bis_custom_delivery_address on bis_flower_order.custom_id " +
//                "= bis_custom_delivery_address.custom_id LEFT JOIN china_area on bis_custom_delivery_address.area_id = china_area.id  " +
//                "where \n" +
//                "bis_flower_order.base_id = 1704435229558108162 and bis_custom_delivery_address.area_id = " +
//                "10 and DATE_FORMAT(bis_flower_order.place_order_time, '%Y-%m-%d')" +
//                " BETWEEN '202-131-05' AND '2023-11-08' GROUP BY bis_custom_delivery_address.area_id;";
            ResultSet resultSet2 = statement.executeQuery(sql2);
            while (resultSet2.next()) {
                RegionalAmount regionalAmount = new RegionalAmount();
                regionalAmount.setRegional(resultSet2.getString(1));
                regionalAmount.setAmout(resultSet2.getString(2));
                regionalAmountList.add(regionalAmount);
            }
            costom.setRegionalAmounts(regionalAmountList);
            for (RegionalAmount regionalAmount : costom.getRegionalAmounts()) {
                System.out.println(regionalAmount);
            }


        }
    }